﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;
using famails.common;
using famails.dto;
using famails.App_Code.source.dto;

namespace famails.dao
{
    /// <summary>
    /// Summary description for ProductGroupDTO
    /// </summary>
    public class CM_ProductGroupDAO
    {
        public CM_ProductGroupDAO()
        {

        }
        public DataTable GetByID(int ID, int UserID)
        {
            SqlCommand cmd = new SqlCommand("select * From tblCM_ProductGroups where productgroupid = "+ID, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable GetAll(int userid)
        {
            SqlCommand cmd = new SqlCommand("SELECT a.* FROM tblCM_ProductGroups a WHERE a.isdelete <> 1 and createby="+userid, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }


        public bool Insert(CM_ProductGroupDTO dto)
        {
            SqlCommand cmd = new SqlCommand("insert into tblCM_ProductGroups(ProductGroupName, GroupStep, CreateDate, createBy, parentid)values(@name, @GroupStep, getdate(), @createby, @parentid)", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@GroupStep", SqlDbType.Int).Value = dto.GroupStep;
            cmd.Parameters.Add("@CreateBy", SqlDbType.Int).Value = dto.CreateBy;
            cmd.Parameters.Add("@parentid", SqlDbType.Int).Value = dto.ParentID;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = dto.ProductGroupName;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        public bool Edit(CM_ProductGroupDTO dto)
        {
            SqlCommand cmd = new SqlCommand("update tblCM_ProductGroups set ProductGroupName=@name, GroupStep =@GroupStep, parentid=@parentid where ProductGroupid = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = dto.ProductGroupName;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = dto.ProductGroupID;
            cmd.Parameters.Add("@parentid", SqlDbType.Int).Value = dto.ParentID;
            cmd.Parameters.Add("@GroupStep", SqlDbType.Int).Value = dto.GroupStep;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        public bool Delete(int id)
        {
            SqlCommand cmd = new SqlCommand("update tblCM_ProductGroups set isdelete=1 where ProductGroupid = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        internal bool CheckExistsName(string name, int id)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblCM_ProductGroups WHERE ProductGroupid <> @id and ProductGroupname = @name and isdelete=0", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table.Rows.Count > 0;
        }

        internal bool CheckBeforeDelete(int id)
        {
            SqlCommand cmd = new SqlCommand("SELECT Productid FROM tblCM_Products WHERE GroupID = @id and isdelete=0", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table.Rows.Count == 0;
        }

        internal DataTable GetProductGroup(int userid, int groupstep)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblCM_ProductGroups WHERE groupstep="+groupstep+" and isdelete=0 and (createby = "+userid+" or createby = (select userid from tblclient where clientid = (select clientid from tblsubclient where userid="+userid+")))", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal DataTable GetParentGroup(int userid, string GroupStep)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblCM_ProductGroups WHERE groupstep=" + GroupStep + "-1 and isdelete=0 and (createby = "+userid+" or createby = (select userid from tblclient where clientid = (select clientid from tblsubclient where userid="+userid+")))", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal DataTable GetChildGroup(int parentid,int groupstep, int userid)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblCM_ProductGroups WHERE (createby = " + userid + " or createby = (select userid from tblclient where clientid = (select clientid from tblsubclient where userid=" + userid + "))) and  groupstep=" + groupstep + " and (-1=" + parentid + " or parentid=" + parentid + ") and isdelete=0 ", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
    }
}
